import ibm_db_dbi
import pymssql
import pymysql
import pyodbc
import cx_Oracle
from config.config import Config


class DB():
    def __init__(self):
        self.pool = None
        self.conn = None
        self.cursor = None

    def __del__(self):
        if self.cursor is not None: self.cursor.close()
        if self.conn is not None: self.conn.close()

    def db2_tk_info_connect(self):
        db2_tk_info = Config.read(section='db2_tk_info')

        self.conn = ibm_db_dbi.connect(
            f"database={db2_tk_info['database']};hostname={db2_tk_info['hostname']};port={db2_tk_info['port']};protocol={db2_tk_info['protocol']};uid={db2_tk_info['uid']};pwd={db2_tk_info['pwd']};",
            user=db2_tk_info['user'],
            password=db2_tk_info['password'])
        self.cursor = self.conn.cursor()
        return self

    def fht_connect(self):
        # cx_Oracle.init_oracle_client(lib_dir=r'D:/env/Driver/Oracle/instantclient_21_12')
        # self.conn = cx_Oracle.connect(user="user", password="pw", dsn=cx_Oracle.makedsn(host="0.0.0.0", port="1521", service_name="db") )  # 连接数据库
        self.conn = cx_Oracle.connect(user="user", password="pw",
                                      dsn="0.0.0.0:1521/db")  # 连接数据库

        self.cursor = self.conn.cursor()
        return self

    def mssql_taikang_connect_pymssql(self):
        mssql_taikang = Config.read(section='mssql_taikang')

        # 查询中文会乱码
        self.conn = pymssql.connect(host=mssql_taikang['host'],
                                    user=mssql_taikang['uid'],
                                    password=mssql_taikang['pwd'],
                                    database=mssql_taikang['database'],
                                    charset='cp936')
        self.cursor = self.conn.cursor()
        return self

    def mssql_taikang_connect(self):
        mssql_taikang = Config.read(section='mssql_taikang')

        self.conn = pyodbc.connect(
            f"DRIVER={{sql server}};SERVER={mssql_taikang['server']};DATABASE={mssql_taikang['database']};UID={mssql_taikang['uid']};PWD={mssql_taikang['pwd']}"
        )
        self.cursor = self.conn.cursor()
        return self

    def mssql_connect3326(self):
        self.conn = pymssql.connect(host='0.0.0.0', port=1433, user='user', password='pw',
                                    database='db')
        self.cursor = self.conn.cursor()
        # self.conn = pyodbc.connect('DRIVER={sql server};SERVER=0.0.0.0,1433;DATABASE=db;UID=user;PWD=pw')
        # self.cursor = self.conn.cursor()
        return self

    def mysql_connect(self):
        mysql_tk = Config.read(section='mysql_tk')

        # 数据库对象
        self.conn = pymysql.connect(host=mysql_tk['host'], user=mysql_tk['user'], passwd=mysql_tk['passwd'],
                                    port=mysql_tk['port'], db=mysql_tk['db'], charset=mysql_tk['charset'])

        self.cursor = self.conn.cursor()
        return self

    def get(self, sql, handle=True):
        print(sql)
        if not handle: return

        if self.pool is not None:
            self.conn = self.pool.Pool.connection()
            self.cursor = self.conn.cursor()

        self.cursor.execute(sql)
        # <class 'tuple'> (,)
        fetchall = self.cursor.fetchall()
        print(f'query length {len(fetchall)}')
        return fetchall

    def get_new(self, sql):
        print(sql)
        cursor = self.conn.get_connection().cursor().cursor
        cursor.execute(sql)
        # <class 'tuple'> (,)
        return cursor.fetchall()

    def get_export(self, rnt_list):
        '''
        rnt_list: db.get_export(db.get(sql))
        '''
        rnt_title = [tuple([des[0] for des in self.cursor.description])]
        return rnt_title + rnt_list


db = DB()

if __name__ == '__main__':
    print(0)
    db = db.db2_tk_info_connect()
    res = db.get_export(db.get(''))
    print(res)
